package com.my.yc.util;

import java.io.IOException;
import java.io.InputStream;
// java 反射包 java.lang.reflect
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class DBHelper {

	private static String url;
	private static String user;
	private static String pwd;
	private static Properties props = new Properties();
	private static ThreadLocal<Connection> localConnection = new ThreadLocal<>();

	static {
		InputStream in = null;
		try {
			in = DBHelper.class.getClassLoader().getResourceAsStream("conn.properties");
			if(in==null) {
				throw new RuntimeException("未读取到项目根目录下的配置文件【conn.properties】");
			}
			props.load(in);
			init(props.getProperty("default"));
		} catch (Exception e) {
			throw new RuntimeException("配置文件【conn.properties】读入失败！", e);
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					throw new RuntimeException(e);
				}
			}
		}
	}

	public static void init(String dbname) {
		init(props.getProperty(dbname + "-url"), props.getProperty(dbname + "-user"),
				props.getProperty(dbname + "-pwd"), props.getProperty(dbname + "-driver"));
	}

	public static void init(String url, String user, String pwd, String driver) {
		try {
			DBHelper.url = url;
			DBHelper.user = user;
			DBHelper.pwd = pwd;
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException("数据库驱动【" + driver + "】加载失败", e);
		} catch (Exception e) {
			throw new RuntimeException("DBHelper初始化失败！", e);
		}
	}

	/**
	 * 获取连接
	 *
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = localConnection.get();
		if (conn != null)
			return conn;
		else
			try {
				return DriverManager.getConnection(url, user, pwd);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
	}

	/**
	 * 带事务执行
	 *
	 * @param handler
	 */
	public static <T> T execute(Handler<T> handler) {
		Connection conn = getConnection();
		try {
			localConnection.set(conn);
			conn.setAutoCommit(false);
			T ret = handler.execute(conn);
			conn.commit();
			return ret;
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception e1) {
				throw new RuntimeException(e1);
			}
			throw new RuntimeException("SQL语句执行错误！", e);
		} finally {
			localConnection.remove();
			try {
				conn.close();
			} catch (Exception e1) {
				throw new RuntimeException(e1);
			}
		}
	}

	/**
	 * 执行增删改SQL语句
	 *
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int update(String sql, Object... params) {
		return _execute(c -> {
			return prepareStatement(c, null, sql, params).executeUpdate();
		});
	}

	public static <T> T selectOneBean(String sql, Class<T> cls, Object... params) {
		return queryOne(sql, new BeanConverter<T>(cls), params);
	}

	public static Map<String, Object> selectOneMap(String sql, Object... params) {
		return queryOne(sql, MAP_CONVERTER, params);
	}

	public static <T> List<T> selectListBean(String sql, Class<T> cls, Object... params) {
		return queryList(sql, new BeanConverter<T>(cls), params);
	}

	public static List<Map<String, Object>> selectListMap(String sql, Object... params) {
		return queryList(sql, MAP_CONVERTER, params);
	}

	public static <T> T selectValue(String sql, Class<T> cls, Object... params) {
		return queryOne(sql, new ValueConverter<T>(cls), params);
	}

	public static long count(String sql, Object... params) {
		sql = "select count(*) from (" + sql + ") a";
		return selectValue(sql, long.class, params);
	}

	/**
	 * 查询List结果
	 */
	public static <T> List<T> queryList(String sql, Converter<T> converter, Object... params) {
		return _execute(c -> {
			PreparedStatement ps = prepareStatement(c, null, sql, params);
			ResultSet rs = ps.executeQuery();
			List<T> ret = new ArrayList<T>();
			for (; rs.next();) {
				T t = converter.convert(rs);
				ret.add(t);
			}
			return ret;
		});
	}

	/**
	 * 查询单行结果
	 */
	public static <T> T queryOne(String sql, Converter<T> converter, Object... params) {
		return _execute(c -> {
			PreparedStatement ps = prepareStatement(c, null, sql, params);
			// 执行语句
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				T ret = converter.convert(rs);
				if (rs.next()) {
					throw new RuntimeException("结果集返回的记录行数大于1！");
				}
				return ret;
			}
			return null;
		});
	}

	/**
	 * 将数据库字段的名称转成 java 驼峰命名方式
	 * 例如： user_name ==> userName
	 */
	public static String toJavaName(String columnName) {
		// 先将字符串全部转成小写
		columnName = columnName.toLowerCase();
		// 判断是否包含下划线
		while (columnName.contains("_")) {
			// 查找下划线
			int i = columnName.indexOf("_");
			// 获取下划线后面的字符
			char c = columnName.charAt(i + 1);
			// 转成大写
			c = Character.toUpperCase(c);
			// 替换小写字符
			columnName = columnName.substring(0, i) + c + columnName.substring(i + 2);
		}
		return columnName;
	}

	/**
	 * 将驼峰命名的名称转成数据库方式
	 * 例如： userName ==> user_name
	 */
	public static String toDBName(String fieldName) {
		return fieldName.replaceAll("([A-W])", "_$1");
	}

	/**
	 * 转换器接口, 将结果集的当前行, 转成特定的对象
	 */
	public static interface Converter<T> {
		T convert(ResultSet rs) throws SQLException;
	}

	/**
	 * 结果集转 Map
	 */
	public final static Converter<Map<String, Object>> MAP_CONVERTER = new Converter<Map<String, Object>>() {
		@Override
		public Map<String, Object> convert(ResultSet rs) throws SQLException {
			Map<String, Object> row = new LinkedHashMap<>();
			for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
				row.put(rs.getMetaData().getColumnName(i + 1).toLowerCase(), rs.getObject(i + 1));
			}
			return row;
		}
	};

	/**
	 * 结果集转 Bean
	 */
	public final static class BeanConverter<T> implements Converter<T> {

		private Class<T> cls;

		public BeanConverter(Class<T> cls) {
			this.cls = cls;
		}

		@Override
		public T convert(ResultSet rs) throws SQLException {
			T ret;
			try {
				ret = cls.newInstance();
				for (Field f : cls.getDeclaredFields()) {
					String cname = toDBName(f.getName());
					String ctype = f.getType().getSimpleName();
					String methodName = buildRSGetMethodName(ctype);
					Method getMethod = rs.getClass().getMethod(methodName, String.class);
					Object value = getMethod.invoke(rs, cname);
					f.setAccessible(true);
					f.set(ret, value);
				}
			} catch (InstantiationException | IllegalAccessException e) {
				throw new RuntimeException("创建实体对象失败!", e);
			} catch (NoSuchMethodException | SecurityException e) {
				throw new RuntimeException("获取 ResultSet 取值方法失败!", e);
			} catch (IllegalArgumentException | InvocationTargetException e) {
				throw new RuntimeException("执行 ResultSet 取值方法失败!", e);
			}
			return ret;
		}

	}

	/**
	 * 结果集转 单值
	 */
	public final static class ValueConverter<T> implements Converter<T> {

		private Class<T> cls;

		public ValueConverter(Class<T> cls) {
			this.cls = cls;
		}

		@SuppressWarnings("unchecked")
		@Override
		public T convert(ResultSet rs) throws SQLException {
			T ret;
			try {
				String ctype = cls.getSimpleName();
				String methodName = buildRSGetMethodName(ctype);
				Method getMethod = rs.getClass().getMethod(methodName, int.class);
				ret = (T) getMethod.invoke(rs, 1);
			} catch (NoSuchMethodException | SecurityException e) {
				throw new RuntimeException("获取 ResultSet 取值方法失败!", e);
			} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
				throw new RuntimeException("执行 ResultSet 取值方法失败!", e);
			}
			return ret;
		}

	}

	private static String buildRSGetMethodName(String ctype) {
		if ("Integer".equals(ctype)) {
			ctype = "int";
		}
		ctype = ctype.substring(0, 1).toUpperCase() + ctype.substring(1);
		return "get" + ctype;
	}

	/**
	 * 创建语句对象
	 *
	 * @param columns <ol><li>null 不返回自增列<li>length==0 返回主键自增值 (MySQL)<li>length > 0 返回指定列自增值 (Oracle)</ol>
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static PreparedStatement prepareStatement(String[] columns, String sql, Object... params)
			throws SQLException {
		return prepareStatement(getConnection(), columns, sql, params);
	}

	/**
	 * 创建语句对象
	 *
	 * @param conn
	 * @param columns <ol><li>null 不返回自增列<li>length==0 返回主键自增值 (MySQL)<li>length > 0 返回指定列自增值 (Oracle)</ol>
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static PreparedStatement prepareStatement(Connection conn, String[] columns, String sql, Object... params)
			throws SQLException {
		System.out.println("SQL：" + sql);
		System.out.println("参数：" + java.util.Arrays.toString(params));
		PreparedStatement ps;
		if (columns != null) {
			if (columns.length > 0) {
				ps = conn.prepareStatement(sql, columns);
			} else {
				ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			}
		} else {
			ps = conn.prepareStatement(sql);
		}
		// 设置参数
		for (int i = 0; i < params.length; i++) {
			ps.setObject(i + 1, params[i]);
		}
		return ps;
	}

	private static <T> T _execute(Handler<T> handler) {
		Connection conn = getConnection();
		try {
			return handler.execute(conn);
		} catch (Exception e) {
			throw new RuntimeException("SQL语句执行错误！", e);
		} finally {
			if (conn != localConnection.get()) {
				try {
					conn.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
		}
	}

	public static interface Handler<T> {
		T execute(Connection conn) throws SQLException;
	}

	public static void main(String[] args) {
		System.out.println(DBHelper.count("select * from yh_user"));
	}

}
